Objectives

  • Describe what a data frame is.
  • Load external data from a .csv file into a data frame.
  • Summarize the contents of a data frame.
  • Subset values from data frames.
  • Describe the difference between a factor and a string.
  • Convert between strings and factors.
  • Reorder and rename factors.
  • Change how character strings are handled in a data frame.
  • Examine and change date formats.

Questions

  • What is a data.frame?
  • How can I read a complete csv file into R?
  • How can I get basic summary information about my dataset?
  • How can I change the way R treats strings in my dataset?
  • Why would I want strings to be treated differently?
  • How are dates represented in R and how can I change the format?

The main dataset used in this tutorial is the Danish kongerække digitized by BA students in the Digital Methods class on the basis of online resources in Danmarkshistorien and kongeraekke URL.

You can download the dataset here or use R to download it in your current folder:

# download the dataset
download.file("https://raw.githubusercontent.com/adivea/r-history/main/episodes/data/kings.csv", 
              destfile = "data/kings.csv")

Please refrain from opening the file, we will load and open it first in R, to prevent your operating system from changing the file’s format

What are data frames and tibbles?

Data frames are the de facto data structure for tabular data in R, and what we use for data processing, statistics, and plotting.

A data frame is the representation of data in the format of a table where the columns are vectors that all have the same length. Data frames are analogous to the more familiar spreadsheet in programs such as Excel, with one key difference. Because columns are vectors, each column must contain a single type of data (e.g., characters, integers, factors). For example, here is a figure depicting a data frame comprising a numeric, a character, and a logical vector.

A 3 by 3 data frame with columns showing numeric, character and logical values.

Data frames can be created by hand, but most commonly they are generated by the functions read_csv() or read_table(); in other words, when importing spreadsheets from your hard drive (or the web). We will now demonstrate how to import tabular data using read_csv().

Presentation of the ‘kings’ data

We will be using a subset of the cleaned version of the dataset that was produced through cleaning in OpenRefine (data/kings.csv). In this dataset, the missing data is encoded as “NULL”, each row holds information for a single interview respondent, and the columns represent:

column_name description
Name The unique name of Danish monarch
Start_year starting year of the monarch’s rule
End_year the last year of the monarch’s rule
Birth_year the year the monarch was born
Death_year the year the monarch died
House the royal house the monarch belonged to
Dynasty another term for the royal house

Importing data

You are going to load the data in R’s memory using the function read_csv() from the readr package, which is part of the tidyverse; learn more about the tidyverse collection of packages here. readr gets installed as part as the tidyverse installation. When you load the tidyverse (library(tidyverse)), the core packages (the packages used in most data analyses) get loaded, including readr.

Before proceeding, however, this is a good opportunity to talk about conflicts. Certain packages we load can end up introducing function names that are already in use by pre-loaded R packages. For instance, when we load the tidyverse package below, we will introduce two conflicting functions: filter() and lag(). This happens because filter and lag are already functions used by the stats package (already pre-loaded in R). What will happen now is that if we, for example, call the filter() function, R will use the dplyr::filter() version and not the stats::filter() one. This happens because, if conflicted, by default R uses the function from the most recently loaded package. Conflicted functions may cause you some trouble in the future, so it is important that we are aware of them so that we can properly handle them, if we want.

To do so, we just need the following functions from the conflicted package:

  • conflicted::conflict_scout(): Shows us any conflicted functions.
  • conflict_prefer("function", "package_prefered"): Allows us to choose the default function we want from now on.

It is also important to know that we can, at any time, just call the function directly from the package we want, such as stats::filter().

Before we can use the read_csv() functions, we need to load the tidyverse packages.

Also, if you recall, the missing data is encoded as “NULL” in the dataset. We’ll tell it to the function, so R will automatically convert all the “NULL” entries in the dataset into NA.

library(tidyverse)

kings <- read_csv("data/kings.csv", na = c("NULL",""), n_max = 54)

In the above code, we notice the read_csv() function takes folder and file names as inputs (e.g., "data/kings.csv"), each enclosed in quotations ("") and separated by a comma.

If you were to type in the code above, it is likely that the read.csv() function would appear in the automatically populated list of functions. This function is different from the read_csv() function, as it is included in the “base” packages that come pre-installed with R. Overall, read.csv() behaves similar to read_csv(), with a few notable differences. First, read.csv() coerces column names with spaces and/or special characters to different names. Second, read.csv() stores data as a data.frame, where read_csv() stores data as a tibble. We prefer tibbles because they have nice printing properties among other desirable qualities. Read more about tibbles here.

The second statement in the code above creates a data frame but doesn’t output any data because, as you might recall, assignments (<-) don’t display anything. (Note, however, that read_csv may show informational text about the data frame that is created.) If we want to check that our data has been loaded, we can see the contents of the data frame by typing its name: kings in the console.

kings
## Try also
## view(kings)
## head(kings)
# A tibble: 54 × 11
   Name        House Start…¹ End_y…² Birth…³ Death…⁴ Gender Dynasty Source Birth…⁵ Death…⁶
   <chr>       <chr>   <dbl>   <dbl>   <dbl>   <dbl> <chr>  <chr>   <chr>  <chr>   <chr>  
 1 Gorm den G… Gorm      936     958     908     958 M      Jellin… https… 908     958    
 2 Harald 1. … Gorm       NA      NA     936     985 M      Jellin… https… 936     987    
 3 Toke_Gorms… Gorm      985     986      NA     986 M      Jellin… https… <NA>    <NA>   
 4 Svend 1. T… Gorm       NA      NA      NA    1014 M      Jellin… https… 17/04/… 03/02/…
 5 Harald 2.   Gorm     1014    1018     963      NA M      Jellin… https… 994     1018   
 6 Knud 1. de… Gorm     1018    1035      NA    1035 M      Jellin… https… 995     12/11/…
 7 Hardeknud   Gorm     1035    1042     995    1042 M      Jellin… https… 1018    08/06/…
 8 Magnus den… Fair…    1042    1047    1018    1047 M      Jellin… https… 1024    25/10/…
 9 Svend 2. E… Estr…    1047    1074    1024    1076 M      <NA>    https… 1019    28/04/…
10 Harald 3. … Estr…    1074    1080    1019    1080 M      Jellin… https… 1041    17/04/…
# … with 44 more rows, and abbreviated variable names ¹​Start_year, ²​End_year,
#   ³​Birth_year, ⁴​Death_year, ⁵​BirthDMY, ⁶​DeathDMY

Note

read_csv() assumes that fields are delimited by commas. However, in several countries, the comma is used as a decimal separator and the semicolon (;) is used as a field delimiter. If you want to read in this type of files in R, you can use the read_csv function. It behaves exactly like read_csv but uses different parameters for the decimal and the field separators. If you are working with another format, they can be both specified by the user. Check out the help for read_csv() by typing ?read_csv to learn more. There is also the read_tsv() for tab-separated data files, and read_delim() allows you to specify more details about the structure of your file.

Note that read_csv() actually loads the data as a tibble. A tibble is an extension of R data frames used by the tidyverse. When the data is read using read_csv(), it is stored in an object of class tbl_df, tbl, and data.frame. You can see the class of an object with

class(kings)
class(kings$Start_year)
[1] "spec_tbl_df" "tbl_df"      "tbl"         "data.frame" 
[1] "numeric"

As a tibble, the type of data included in each column is listed in an abbreviated fashion below the column names. For instance, here Danish_kings is a column of characters (<chr>), Start_year and End_year are columns of floating point numbers (abbreviated <dbl> for the word ‘double’).

Inspecting data frames

When calling a tbl_df object (like kings here), there is already a lot of information about our data frame being displayed such as the number of rows, the number of columns, the names of the columns, and as we just saw the class of data stored in each column. However, there are functions to extract this information from data frames. Here is a non-exhaustive list of some of these functions. Let’s try them out!

Size:

  • dim(kings) - returns a vector with the number of rows as the first element, and the number of columns as the second element (the dimensions of the object)
  • nrow(kings) - returns the number of rows
  • ncol(kings) - returns the number of columns

Content:

  • head(kings) - shows the first 6 rows
  • tail(kings) - shows the last 6 rows

Names:

  • names(kings) - returns the column names (synonym of colnames() for data.frame objects)

Summary:

  • str(kings) - structure of the object and information about the class, length and content of each column
  • summary(kings) - summary statistics for each column
  • glimpse(kings) - returns the number of columns and rows of the tibble, the names and class of each column, and previews as many values will fit on the screen. Unlike the other inspecting functions listed above, glimpse() is not a “base R” function so you need to have the dplyr or tibble packages loaded to be able to execute it.

Note: most of these functions are “generic.” They can be used on other types of objects besides data frames or tibbles.

Subsetting data frames

Our kings data frame has rows and columns (it has 2 dimensions). In practice, we may not need the entire data frame; for instance, we may only be interested in a subset of the observations (the rows) or a particular set of variables (the columns). If we want to access some specific data from it, we need to specify the “coordinates” (i.e., indices) we want from it. Row numbers come first, followed by column numbers.

Tip

Subsetting a tibble with [ always results in a tibble. However, note this is not true in general for data frames, so be careful! Different ways of specifying these coordinates can lead to results with different classes. This is covered in the Software Carpentry lesson R for Reproducible Scientific Analysis.
## first element in the first column of the tibble
kings[1, 1]
# A tibble: 1 × 1
  Name          
  <chr>         
1 Gorm den Gamle
## first element in the 6th column of the tibble 
kings[1, 6]
# A tibble: 1 × 1
  Death_year
       <dbl>
1        958
## first column of the tibble (as a vector)
kings[[1]]
 [1] "Gorm den Gamle"                 "Harald 1. Blåtand"             
 [3] "Toke_Gormsen"                   "Svend 1. Tveskæg"              
 [5] "Harald 2."                      "Knud 1. den Store"             
 [7] "Hardeknud"                      "Magnus den Gode"               
 [9] "Svend 2. Estridsen"             "Harald 3. Hen"                 
[11] "Knud 2. den Hellige"            "Oluf 1. Hunger"                
[13] "Erik 1. Ejegod"                 "Niels"                         
[15] "Erik 2. Emune"                  "Erik 3. Lam"                   
[17] "Svend 3.; Knud 3.; Valdemar 1." "Valdemar 1. den Store"         
[19] "Knud 4."                        "Valdemar 2. Sejr"              
[21] "Erik 4. Plovpenning"            "Abel"                          
[23] "Christoffer 1."                 "Erik 5. Klipping"              
[25] "Erik 6. Menved"                 "Christoffer 2."                
[27] "Valdemar 3."                    "Christoffer 2."                
[29] "Valdemar 4. Atterdag"           "Oluf 2."                       
[31] "Margrete 1."                    "Erik 7. af Pommern"            
[33] "Christoffer 3. af Bayern"       "Chrstian 1."                   
[35] "Hans"                           "Christian 2."                  
[37] "Frederik 1."                    "Christian 3."                  
[39] "Frederik 2."                    "Christian 4."                  
[41] "Frederik 3."                    "Christian 5."                  
[43] "Frederik 4."                    "Christian 6."                  
[45] "Frederik 5."                    "Christian 7."                  
[47] "Frederik 6."                    "Christian 8."                  
[49] "Frederik 7."                    "Christian 9."                  
[51] "Frederik 8."                    "Christian 10."                 
[53] "Frederik 9."                    "Margrete 2."                   
## first column of the tibble
kings[1]
# A tibble: 54 × 1
   Name              
   <chr>             
 1 Gorm den Gamle    
 2 Harald 1. Blåtand 
 3 Toke_Gormsen      
 4 Svend 1. Tveskæg  
 5 Harald 2.         
 6 Knud 1. den Store 
 7 Hardeknud         
 8 Magnus den Gode   
 9 Svend 2. Estridsen
10 Harald 3. Hen     
# … with 44 more rows
## first three elements in the 7th column of the tibble
kings[1:3, 7]
# A tibble: 3 × 1
  Gender
  <chr> 
1 M     
2 M     
3 M     
## the 3rd row of the tibble
kings[3, ]
# A tibble: 1 × 11
  Name         House Start…¹ End_y…² Birth…³ Death…⁴ Gender Dynasty Source Birth…⁵ Death…⁶
  <chr>        <chr>   <dbl>   <dbl>   <dbl>   <dbl> <chr>  <chr>   <chr>  <chr>   <chr>  
1 Toke_Gormsen Gorm      985     986      NA     986 M      Jellin… https… <NA>    <NA>   
# … with abbreviated variable names ¹​Start_year, ²​End_year, ³​Birth_year, ⁴​Death_year,
#   ⁵​BirthDMY, ⁶​DeathDMY
## equivalent to head_kings <- head(kings)
head_kings <- kings[1:6, ]

: is a special function that creates numeric vectors of integers in increasing or decreasing order, test 1:10 and 10:1 for instance.

You can also exclude certain indices of a data frame using the “-” sign:

kings[, -1]          # The whole tibble, except the first column
# A tibble: 54 × 10
   House     Start_year End_year Birth_year Death_…¹ Gender Dynasty Source Birth…² Death…³
   <chr>          <dbl>    <dbl>      <dbl>    <dbl> <chr>  <chr>   <chr>  <chr>   <chr>  
 1 Gorm             936      958        908      958 M      Jellin… https… 908     958    
 2 Gorm              NA       NA        936      985 M      Jellin… https… 936     987    
 3 Gorm             985      986         NA      986 M      Jellin… https… <NA>    <NA>   
 4 Gorm              NA       NA         NA     1014 M      Jellin… https… 17/04/… 03/02/…
 5 Gorm            1014     1018        963       NA M      Jellin… https… 994     1018   
 6 Gorm            1018     1035         NA     1035 M      Jellin… https… 995     12/11/…
 7 Gorm            1035     1042        995     1042 M      Jellin… https… 1018    08/06/…
 8 Fairhair        1042     1047       1018     1047 M      Jellin… https… 1024    25/10/…
 9 Estridsen       1047     1074       1024     1076 M      <NA>    https… 1019    28/04/…
10 Estridsen       1074     1080       1019     1080 M      Jellin… https… 1041    17/04/…
# … with 44 more rows, and abbreviated variable names ¹​Death_year, ²​BirthDMY, ³​DeathDMY
kings[-c(7:54), ]   # Equivalent to head(kings)
# A tibble: 6 × 11
  Name         House Start…¹ End_y…² Birth…³ Death…⁴ Gender Dynasty Source Birth…⁵ Death…⁶
  <chr>        <chr>   <dbl>   <dbl>   <dbl>   <dbl> <chr>  <chr>   <chr>  <chr>   <chr>  
1 Gorm den Ga… Gorm      936     958     908     958 M      Jellin… https… 908     958    
2 Harald 1. B… Gorm       NA      NA     936     985 M      Jellin… https… 936     987    
3 Toke_Gormsen Gorm      985     986      NA     986 M      Jellin… https… <NA>    <NA>   
4 Svend 1. Tv… Gorm       NA      NA      NA    1014 M      Jellin… https… 17/04/… 03/02/…
5 Harald 2.    Gorm     1014    1018     963      NA M      Jellin… https… 994     1018   
6 Knud 1. den… Gorm     1018    1035      NA    1035 M      Jellin… https… 995     12/11/…
# … with abbreviated variable names ¹​Start_year, ²​End_year, ³​Birth_year, ⁴​Death_year,
#   ⁵​BirthDMY, ⁶​DeathDMY

tibbles can be subset by calling indices (as shown previously), but also by calling their column names directly:

kings["Name"]       # Result is a tibble

kings[, "Name"]     # Result is a tibble

kings[["Name"]]     # Result is a vector

kings$Name          # Result is a vector

In RStudio, you can use the autocompletion feature to get the full and correct names of the columns.

Exercise 1

  1. Create a tibble (kings_20) containing only the data in row 20 of the kings dataset.

  2. Notice how nrow() gave you the number of rows in the tibble?

  • Use that number to pull out just that last row in the tibble.
  • Compare that with what you see as the last row using tail() to make sure it’s meeting expectations.
  • Pull out that last row using nrow() instead of the row number.
  • Create a new tibble (kings_last) from that last row.
  1. Using the number of rows in the kings dataset that you found in question 2, extract the row that is in the middle of the dataset. Store the content of this middle row in an object named kings_middle. (hint: This dataset has an odd number of rows, so finding the middle is a bit trickier than dividing n_rows by 2. Use the median( ) function and what you’ve learned about sequences in R to extract the middle row!

  2. Combine nrow() with the - notation above to reproduce the behavior of head(kings), keeping just the first through 6th rows of the kings dataset.

## 1.
kings_20 <- kings[20, ]

## 2.
# Saving `n_rows` to improve readability and reduce duplication
n_rows <- nrow(kings)
kings_last <- kings[n_rows, ]

## 3.
kings_middle <- kings[round(median(1:n_rows)), ]

## 4.
kings_head <- kings[-(7:n_rows), ]

Calculating a new column

Sometimes you wants to create derived data and attach them to the table in a new column. The number of reign years, for example can be calculated by subtracting the starting year from the ending year. The mid-year of the reign can be calculated by subtracting half of reign years from the ending year.

There are two ways of creating new columns: we can use the $ operator or the mutate() function. We will learn the latter in the next lesson, and focus on $ approach here.

First, you define an empty column and then use the assignment key <- to write the product of the subtraction to it.

kings$Reign_duration <- kings$End_year - kings$Start_year

Investigate the result visually. Can you find out what kind of datatype the new column is?

kings # see the entire tibble including the new column
# A tibble: 54 × 12
   Name        House Start…¹ End_y…² Birth…³ Death…⁴ Gender Dynasty Source Birth…⁵ Death…⁶
   <chr>       <chr>   <dbl>   <dbl>   <dbl>   <dbl> <chr>  <chr>   <chr>  <chr>   <chr>  
 1 Gorm den G… Gorm      936     958     908     958 M      Jellin… https… 908     958    
 2 Harald 1. … Gorm       NA      NA     936     985 M      Jellin… https… 936     987    
 3 Toke_Gorms… Gorm      985     986      NA     986 M      Jellin… https… <NA>    <NA>   
 4 Svend 1. T… Gorm       NA      NA      NA    1014 M      Jellin… https… 17/04/… 03/02/…
 5 Harald 2.   Gorm     1014    1018     963      NA M      Jellin… https… 994     1018   
 6 Knud 1. de… Gorm     1018    1035      NA    1035 M      Jellin… https… 995     12/11/…
 7 Hardeknud   Gorm     1035    1042     995    1042 M      Jellin… https… 1018    08/06/…
 8 Magnus den… Fair…    1042    1047    1018    1047 M      Jellin… https… 1024    25/10/…
 9 Svend 2. E… Estr…    1047    1074    1024    1076 M      <NA>    https… 1019    28/04/…
10 Harald 3. … Estr…    1074    1080    1019    1080 M      Jellin… https… 1041    17/04/…
# … with 44 more rows, 1 more variable: Reign_duration <dbl>, and abbreviated variable
#   names ¹​Start_year, ²​End_year, ³​Birth_year, ⁴​Death_year, ⁵​BirthDMY, ⁶​DeathDMY
kings$Reign_duration  # see the data as a vector
 [1] 22 NA  1 NA  4 17  7  5 27  6  6  9  8 30  3  9 11 25 20 39  9  2  7 27 33  7  3  3
[29] 35 12  9 43  8 33 31 10 10 23 29 60 22 29 31 16 20 42 31  9 15 43  6 35 25 51

Now, create another column called Midyear by figuring out what the middle year of each ruler’s reign is. This will be important if we wish to plot the rulers on a graph’s x axis.

kings$Midyear <- kings$End_year - (kings$Reign_duration/2)

Formatting Dates

One of the most common issues that new (and experienced!) R users have is converting date and time information into a variable that is appropriate and usable during analyses. A best practice for dealing with date data is to ensure that each component of your date is available as a separate variable.

We are going to use the package lubridate, which is included in the tidyverse installation but not loaded by default, so we have to load it explicitly with library(lubridate).

Start by loading the required package:

library(lubridate)

The lubridate function ymd() takes a vector representing year, month, and day, and converts it to a Date vector. Date is a class of data recognized by R as being a date and can be manipulated as such. The argument that the function requires is flexible, but, as a best practice, is a character vector formatted as “YYYY-MM-DD”.

Let’s say we have a vector of dates in character format:

char_dates <- c("7/31/2012", "8/9/2014", "4/30/2016")
str(char_dates)
 chr [1:3] "7/31/2012" "8/9/2014" "4/30/2016"

We can convert this vector to dates as :

as_date(char_dates, format = "%m/%d/%Y")
[1] "2012-07-31" "2014-08-09" "2016-04-30"

Argument format tells the function the order to parse the characters and identify the month, day and year. The format above is the equivalent of mm/dd/yyyy. A wrong format can lead to parsing errors or incorrect results.

For example, observe what happens when we use a lower case y instead of upper case Y for the year.

as_date(char_dates, format = "%m/%d/%y")
[1] "2020-07-31" "2020-08-09" "2020-04-30"

Here, the %y part of the format stands for a two-digit year instead of a four-digit year, and this leads to parsing errors.

Or in the following example, observe what happens when the month and day elements of the format are switched.

as_date(char_dates, format = "%d/%m/%y")
[1] NA           "2020-09-08" NA          

Since there is no month numbered 30 or 31, the first and third dates cannot be parsed.

We can also use functions ymd(), mdy() or dmy() to convert character variables to date.

mdy(char_dates)
[1] "2012-07-31" "2014-08-09" "2016-04-30"

Missing data

As R was designed to analyze datasets, it includes the concept of missing data (which is uncommon in other programming languages). Missing data are represented in vectors as NA.

When doing operations on numbers, most functions will return NA if the data you are working with include missing values. This feature makes it harder to overlook the cases where you are dealing with missing data. You can add the argument na.rm=TRUE to calculate the result while ignoring the missing values.

years <- c(2, 1, 1, NA, 7)
mean(years)
[1] NA
max(years)
[1] NA
mean(years, na.rm = TRUE)
[1] 2.75
max(years, na.rm = TRUE)
[1] 7

If your data include missing values, you may want to become familiar with the functions is.na(), na.omit(), and complete.cases(). See below for examples.

## Extract those elements which are not missing values.
## The ! character is also called the NOT operator
years[!is.na(years)]
[1] 2 1 1 7
## Count the number of missing values.
## The output of is.na() is a logical vector (TRUE/FALSE equivalent to 1/0) so the sum() function here is effectively counting
sum(is.na(years))
[1] 1
## Returns the object with incomplete cases removed. The returned object is an atomic vector of type `"numeric"` (or `"double"`).
na.omit(years)
[1] 2 1 1 7
attr(,"na.action")
[1] 4
attr(,"class")
[1] "omit"
## Extract those elements which are complete cases. The returned object is an atomic vector of type `"numeric"` (or `"double"`).
years[complete.cases(years)]
[1] 2 1 1 7

Recall that you can use the typeof() function to find the type of your atomic vector.

Exercise 2

  1. Using the kings dataset, eliminate the NAs from the years of reign.
kings$Reign_duration
 [1] 22 NA  1 NA  4 17  7  5 27  6  6  9  8 30  3  9 11 25 20 39  9  2  7 27 33  7  3  3
[29] 35 12  9 43  8 33 31 10 10 23 29 60 22 29 31 16 20 42 31  9 15 43  6 35 25 51
kings$Reign_duration <- kings$End_year-kings$Start_year
years <- kings$Reign_duration
years_no_na <- years[!is.na(years)]
# or
years_no_na <- na.omit(years)
  1. Use the function mean() to calculate what is the average reign duration.
mean(years, na.rm = TRUE)
[1] 19.57692
  1. Use R to figure out which kings enjoyed more than average years of rule.
years_above_20 <- years_no_na[years_no_na > 20]
length(years_above_20)
[1] 23

Keypoints

  • Use read_csv to read tabular data in R.
  • Use lubridate package to fix dates in R